﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MODEL;
using System.Data;
using System.Data.SqlClient;
using UsersDAL;

namespace DAL
{
    public class UsersDAL
    {
        private Users ToUsers(DataRow row)
        {
            Users model = new Users();
            model.Id = (System.Int64)sqlHelper.GetToDbvalue(row["Id"]);
            model.LoginPwd = (System.String)sqlHelper.GetToDbvalue(row["LoginPwd"]);
            model.FriendshipPolicyId = (System.Int32)sqlHelper.GetToDbvalue(row["FriendshipPolicyId"]);
            model.NickName = (System.String)sqlHelper.GetToDbvalue(row["NickName"]);
            model.FaceId = (System.Int32)sqlHelper.GetToDbvalue(row["FaceId"]);
            model.Sex = (System.String)sqlHelper.GetToDbvalue(row["Sex"]);
            model.Age = (System.Int32)sqlHelper.GetToDbvalue(row["Age"]);
            model.Name = (System.String)sqlHelper.GetToDbvalue(row["Name"]);
            model.StarId = (System.Int32)sqlHelper.GetToDbvalue(row["StarId"]);
            model.BloodTypeId = (System.Int32)sqlHelper.GetToDbvalue(row["BloodTypeId"]);
            model.Email = (System.String)sqlHelper.GetToDbvalue(row["Email"]);
            model.IP = (System.String)sqlHelper.GetToDbvalue(row["IP"]);
            model.Sign = (System.String)sqlHelper.GetToDbvalue(row["Sign"]);
            model.IsLogin = (System.Boolean)sqlHelper.GetToDbvalue(row["IsLogin"]);
            return model;
        }
        /// <summary>
        /// 登录验证
        /// </summary>
        /// <param name="id">ID</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public Users Login(long id,string password)
        {
          DataTable table=  sqlHelper.ExecuteDataTable("select * from Users where Id=@Id and LoginPwd=@LoginPwd",
                 new SqlParameter("@Id", id)
            , new SqlParameter("@LoginPwd", password));
            if (table.Rows.Count==1)
	            {
                            Users user=new Users();
                DataRow row=table.Rows[0];
               user= ToUsers(row);
		             return user;
	            }
            else
	            {
                           return null;
	            }
        }
        /// <summary>
        /// 每次登录时更新本次IP
        /// </summary>
        /// <param name="id">ID</param>
        public void UpDateIp(long id ,string ip)
        {
         int x=   sqlHelper.ExecuteNonQuery("update Users set IP=@IP where Id=@Id"
                   , new SqlParameter("@Id", id)
                    , new SqlParameter("@IP", ip));
        }
        public DataTable GetAll()
        {
            DataTable table = sqlHelper.ExecuteDataTable("select Id,NickName,Sex,Age,Name,IP,Email  from Users");
            //Users[] models = new Users[table.Rows.Count];
            //for (int i = 0; i < table.Rows.Count; i++)
            //{
            //    DataRow row = table.Rows[i];
            //    models[i] = ToUsers(row);
            //}
           return table;
        }
        public Users[] GetAll0()
        {
            DataTable table = sqlHelper.ExecuteDataTable("select * from Users");
            Users[] models = new Users[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                models[i] = ToUsers(row);
            }
            return models;
        }
         public DataTable GetAllsearch()
        {
            DataTable table = sqlHelper.ExecuteDataTable("select Id,NickName,Sex,Age,Name,Email,Sign  from Users");
                  return table;
         }
        public void Insert(Users moble)
        {
            sqlHelper.ExecuteNonQuery("insert into Users(Id,LoginPwd,FriendshipPolicyId,NickName,FaceId,Sex,Age,Name,StarId,BloodTypeId)values(@Id,@LoginPwd,@FriendshipPolicyId,@NickName,@FaceId,@Sex,@Age,@Name,@StarId,@BloodTypeId)"
            , new SqlParameter("@Id", moble.Id)
            , new SqlParameter("@LoginPwd", moble.LoginPwd)
            , new SqlParameter("@FriendshipPolicyId",1)
            , new SqlParameter("@NickName", moble.NickName)
            , new SqlParameter("@FaceId",1)
            , new SqlParameter("@Sex", moble.Sex)
            , new SqlParameter("@Age",moble.Age)
            , new SqlParameter("@Name", moble.Name)
            , new SqlParameter("@StarId", moble.StarId)
            , new SqlParameter("@BloodTypeId", moble.BloodTypeId)
            );
        }
        public Users SelectById(long id)
        {
            DataTable table = sqlHelper.ExecuteDataTable("select * from Users where Id=@Id ",
               new SqlParameter("@Id", id));
            if (table.Rows.Count == 1)
            {
                Users user = new Users();
                DataRow row = table.Rows[0];
                user = ToUsers(row);
                return user;
            }
            else
            {
                return null;
            }
        }
        public Users SelectByIp(string ip)
        {
            DataTable table = sqlHelper.ExecuteDataTable("select * from Users where IP=@IP ",
               new SqlParameter("@IP", ip));
            if (table.Rows.Count == 1)
            {
                Users user = new Users();
                DataRow row = table.Rows[0];
                user = ToUsers(row);
                return user;
            }
            else
            {
                return null;
            }
        }
        public DataTable SelectByIpTable(string ip)
        {
            DataTable table = sqlHelper.ExecuteDataTable("select * from Users where IP=@IP ",
               new SqlParameter("@IP", ip));
            return table;
        }
        public DataTable SelectByIdTable(long id)
        {
            DataTable table = sqlHelper.ExecuteDataTable("select Id,NickName,Sex,Age,Name,Email,Sign from Users where Id=@Id ",
               new SqlParameter("@Id", id));
            return table;
        }

        public DataTable SelectBySexAgeTable(int minage,int maxage,string sex)
        {
            DataTable table = sqlHelper.ExecuteDataTable("select Id,NickName,Sex,Age,Name,Email,Sign from Users where Age>@minage and Age<@maxage and sex=@sex"
              , new SqlParameter("@minage", minage)
               , new SqlParameter("@maxage", maxage)
                , new SqlParameter("@sex", sex)
              );
            return table;
        }
        public long[] GetAllFriendsId(long id)
        {
            DataTable table = sqlHelper.ExecuteDataTable("select FriendId from Friends where HostId=@HostId"
                , new SqlParameter("@HostId", id));
            long[] FriendsId = new long[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                FriendsId[i] = (System.Int64)sqlHelper.GetToDbvalue(row["FriendId"]);
            }
            return FriendsId;
        }
        public bool UpDateFaceId(int faceId, long Id)
        {
          int result=  sqlHelper.ExecuteNonQuery("UpDate Users set FaceId=@faceId where Id=@Id",
                new SqlParameter("@faceId", faceId)
                , new SqlParameter("@Id", Id));
          if (result==1)
          {
              return true;
          }
          else
          {
              return false;
          }
        }
        public bool UpDateIsLogin(bool islogin, long Id)
        {
            int result = sqlHelper.ExecuteNonQuery("UpDate Users set IsLogin=@IsLogin where Id=@Id",
                  new SqlParameter("@IsLogin", islogin)
                  , new SqlParameter("@Id", Id));
            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool UpDateDetila(Users moble, long id)
            {
                int result = sqlHelper.ExecuteNonQuery("update Users set Id=@newId ,Age=@Age, Sex=@Sex,  StarId=@StarId, BloodTypeId=@BloodTypeId, Name=@Name, NickName=@NickName where Id=@oldId"
                   , new SqlParameter("@newId", moble.Id)
            , new SqlParameter("@NickName", moble.NickName)
            , new SqlParameter("@Sex", moble.Sex)
            , new SqlParameter("@Age", moble.Age)
            , new SqlParameter("@Name", moble.Name)
            , new SqlParameter("@StarId", moble.StarId)
            , new SqlParameter("@BloodTypeId", moble.BloodTypeId)
              , new SqlParameter("@oldId", id));
                if (result == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        public bool UpDateLimits(int FriendshipPolicyId, string newpassword, long id)
        {
            int result = sqlHelper.ExecuteNonQuery("UpDate Users set FriendshipPolicyId=@FriendshipPolicyId,LoginPwd=@LoginPwd where Id=@Id",
                  new SqlParameter("@FriendshipPolicyId", FriendshipPolicyId)
                  , new SqlParameter("@LoginPwd", newpassword)
                  , new SqlParameter("@Id", id));
            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
